﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel;
using System.IO;
using Clothes.Masters.ModuleData;
using ApplicationLibrary.SqlConnector;
using System.Text.RegularExpressions;
using System.Globalization;

namespace Clothes.Masters.Forms
{
    public partial class CustomerImport : ApplicationLibrary.DBManager.BatchForm
    {
        DRMasters drMasters = new DRMasters(true);

        public CustomerImport()
        {
            InitializeComponent();
        }

        protected override void OnAttachData()
        {
            
        }

        private void btnFolder_Click(object sender, EventArgs e)
        {
            fbdSelect.SelectedPath = txtFolder.Text;
            if (fbdSelect.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                txtFolder.Text = fbdSelect.SelectedPath;
        }

        protected override void OnBatchExecute()
        {
            int maxCode = int.MinValue;

            if (!Directory.Exists(txtFolder.Text))
                return;

            ImportCustomer(txtFolder.Text, ref maxCode);
            base.OnBatchExecute();
        }

        private void ImportCustomer(string path, ref int maxCode)
        {
            
            foreach (string folder in Directory.GetDirectories(path))
                ImportCustomer(folder, ref maxCode);

            foreach (string file in Directory.GetFiles(path, "*.xls"))
            {
                System.Diagnostics.Debug.WriteLine(file);

                string name, address, city, county, zipcode, fiscalcode, email, document;
                int code;
                bool bOk = ReadCustomerData(file, out code, out name, out address, out city, out county, out fiscalcode, out zipcode, out email, out document);

                if (bOk)
                {
                    if (code > maxCode)
                        maxCode = code;

                    AddNewCustomer(code, name, address, city, county, fiscalcode, zipcode, email, document);
                }
                else
                {
                    txtOutput.Text = txtOutput.Text + file + "\r\n";
                    Application.DoEvents();
                }
            }
        }

        private void AddNewCustomer(int code, string name, string address, string city, string county, string fiscalcode, string zipcode, string email, string document)
        {
            if (drMasters.Find(code))
                return;

            DataRow row = drMasters.AddRecord();
            DataRowValues.SetValue<int>(row, CL_Masters.ID, code);
            DataRowValues.SetValue<string>(row, CL_Masters.Description, name);
            DataRowValues.SetValue<string>(row, CL_Masters.Address, address);
            DataRowValues.SetValue<string>(row, CL_Masters.City, city);
            DataRowValues.SetValue<string>(row, CL_Masters.County, county);
            DataRowValues.SetValue<string>(row, CL_Masters.ZipCode, zipcode);
            DataRowValues.SetValue<string>(row, CL_Masters.FiscalCode, fiscalcode);
            DataRowValues.SetValue<string>(row, CL_Masters.Email, email);
            DataRowValues.SetValue<string>(row, CL_Masters.DocNo, document);
            DataRowValues.SetValue<bool>(row, CL_Masters.WarrantSale, true);
            DataRowValues.SetValue<bool>(row, CL_Masters.Notify, true);

            drMasters.Update();
        }

        private bool ReadCustomerData(string filename, out int code, out string name, out string address, out string city, out string county, out string fiscalcode, out string zipcode, out string email, out string document)
        {
            bool bOk = true;
            using (FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read))
            {
                using (IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream))
                {
                    excelReader.IsFirstRowAsColumnNames = false;
                    DataSet result = excelReader.AsDataSet();
                    bOk = int.TryParse(result.Tables["Foglio1"].Rows[2][8].ToString(), out code);
                        
                    name = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(result.Tables["Foglio1"].Rows[3][8].ToString());

                    address = result.Tables["Foglio1"].Rows[4][8].ToString();
                    bOk &= address.Length > 0;
                    string citta = result.Tables["Foglio1"].Rows[5][8].ToString();
                    bOk &= citta.Length > 0;

                    address = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(address);

                    Regex regCity = new Regex("[A-z]+");
                    city = regCity.Match(citta).Value;
                    city = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(city);
                    
                    Regex regZip = new Regex("[0-9]+");
                    zipcode = regZip.Match(citta).Value;

                    county = city.Length > 0 ? city.Substring(0, 2).ToUpper() : "";
                    fiscalcode = result.Tables["Foglio1"].Rows[6][8].ToString().ToUpper();
                    bOk &= fiscalcode.Length > 0;
                    document = result.Tables["Foglio1"].Rows[7][8].ToString();
                    email = result.Tables["Foglio1"].Rows[9][8].ToString();
                    if (email.IndexOf('@') == -1)
                        email = "";
                }
            }
            return bOk;
        }

        private void btnTest_Click(object sender, EventArgs e)
        {
            string name, address, city, county, zipcode, fiscalcode, email, document;
            int code;
            ReadCustomerData(txtFolder.Text, out code, out name, out address, out city, out county, out fiscalcode, out zipcode, out email, out document);
        }
    }
}
